package com.example.farmrental.repository;

import com.example.farmrental.entity.RentalOrder;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;

public interface RentalOrderRepository extends JpaRepository<RentalOrder, Long>, JpaSpecificationExecutor<RentalOrder> {
    boolean existsByOrderNo(String orderNo);

    /**
     * 统计指定状态的订单数量
     */
    long countByStatus(String status);

    /**
     * 统计某个时间段内下单的订单数量
     */
    long countByOrderTimeBetween(java.time.LocalDateTime start, java.time.LocalDateTime end);

    /**
     * 统计某时间段内每天的订单量（用于近七日趋势）
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT DATE(order_time) AS d, COUNT(*) AS c FROM rental_order WHERE order_time >= ?1 AND order_time <= ?2 GROUP BY DATE(order_time) ORDER BY d", nativeQuery = true)
    java.util.List<Object[]> countDaily(java.time.LocalDateTime start, java.time.LocalDateTime end);

    /**
     * 每天营业额（金额汇总）
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT DATE(order_time) AS d, COALESCE(SUM(amount),0) AS s FROM rental_order WHERE order_time >= ?1 AND order_time <= ?2 GROUP BY DATE(order_time) ORDER BY d", nativeQuery = true)
    java.util.List<Object[]> sumDaily(java.time.LocalDateTime start, java.time.LocalDateTime end);

    /**
     * 指定日期的每小时营业额（0-23）
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT HOUR(order_time) AS h, COALESCE(SUM(amount),0) AS s FROM rental_order WHERE DATE(order_time) = ?1 GROUP BY HOUR(order_time) ORDER BY h", nativeQuery = true)
    java.util.List<Object[]> sumHourlyByDate(String date);

    /**
     * 指定时间段内的各状态订单数量
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT status, COUNT(*) AS c FROM rental_order WHERE order_time >= ?1 AND order_time <= ?2 GROUP BY status", nativeQuery = true)
    java.util.List<Object[]> statusGroup(java.time.LocalDateTime start, java.time.LocalDateTime end);

    /**
     * 指定时间段内产生订单的去重客户数
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT COUNT(DISTINCT customer_id) FROM rental_order WHERE customer_id IS NOT NULL AND order_time >= ?1 AND order_time <= ?2", nativeQuery = true)
    long countActiveCustomers(java.time.LocalDateTime start, java.time.LocalDateTime end);

    /**
     * 指定时间段内 新增客户(在此期间注册) 的去重下单客户数
     */
    @org.springframework.data.jpa.repository.Query(value = "SELECT COUNT(DISTINCT ro.customer_id) FROM rental_order ro JOIN customer c ON ro.customer_id=c.id WHERE ro.order_time >= ?1 AND ro.order_time <= ?2 AND c.create_time >= ?1 AND c.create_time <= ?2", nativeQuery = true)
    long countNewActiveCustomers(java.time.LocalDateTime start, java.time.LocalDateTime end);
}
